package com.gang.ssm.bos.system.service.impl;

import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import com.gang.ssm.bos.common.Page;
import com.gang.ssm.bos.core.toolbox.kit.PinYin4jUtils;
import com.gang.ssm.bos.system.bean.Region;
import com.gang.ssm.bos.system.mapper.RegionMapper;
import com.gang.ssm.bos.system.service.RegionService;

@Service
public class RegionServiceImpl implements RegionService{

	@Autowired
	private RegionMapper regionMapper;
	
	@Override
	public void pageQuery(Page<Region> pageBean) {
		int total = (int) regionMapper.countByExample(null);
		List<Region> regions = regionMapper.findByLimit((pageBean.getPageNo() - 1) * 
				pageBean.getPageSize(),pageBean.getPageSize());
		
		pageBean.setTotalCount(total);
		pageBean.setResult(regions);
	}

	@Override
	public List<Region> findAll() {
		return regionMapper.selectByExample(null);
	}

	@Override
	public void save(Region region) {
		String info = region.getProvince() + region.getCity() + region.getDistrict();
		String city = region.getCity();
		String[] headString = PinYin4jUtils.getHeadByString(info);
		String[] cityPinYin = PinYin4jUtils.stringToPinyin(city);
		
		String shortcode = StringUtils.join(headString, "");
		String citycode = StringUtils.join(cityPinYin, "");
		
		region.setShortcode(shortcode);
		region.setCitycode(citycode);
		regionMapper.insertSelective(region);
	}

	@Override
	public Region findById(Integer id) {
		return regionMapper.selectByPrimaryKey(id);
	}

	@Override
	public void deleteById(Integer id) {
		regionMapper.deleteByPrimaryKey(id);
	}

	@Override
	public void importXls(MultipartFile myFile) throws IOException {
		List<Region> regions = buildRegionList(myFile);
		regionMapper.batchInsert(regions);
	}

	private List<Region> buildRegionList(MultipartFile myFile)
			throws IOException {
		
		HSSFWorkbook workbook = new HSSFWorkbook(myFile.getInputStream());
		HSSFSheet sheet = workbook.getSheetAt(0);
		List<Region> regions = new ArrayList<Region>();
		for(Row row : sheet) {
			if(row.getRowNum() == 0) {
				continue;
			}
			
			String province = row.getCell(1).getStringCellValue();
			String city = row.getCell(2).getStringCellValue();
			String district = row.getCell(3).getStringCellValue();
			String postcode = row.getCell(4).getStringCellValue();
			
			Region region = new Region(province, city, district, postcode);
			
			String info = province + city + district;
			String[] handByString = PinYin4jUtils.getHeadByString(info);
			String[] pinyin = PinYin4jUtils.stringToPinyin(city);
			
			String shortcode = StringUtils.join(handByString, "");
			String citycode = StringUtils.join(pinyin, "");
			
			region.setShortcode(shortcode);
			region.setCitycode(citycode);
			
			regions.add(region);
		}
		return regions;
	}

	@Override
	public void exportXls(HttpServletResponse response, 
			HttpServletRequest request) throws IOException {
		
		List<Region> userList = regionMapper.selectByExample(null);
		
		String fileName = "区域数据.xls";
		String contentType = request.getServletContext().getMimeType(fileName);
		response.setContentType(contentType);
		response.setHeader("content-Disposition", "attchment;filename="+ new String(fileName.getBytes(), "ISO-8859-1"));
		
		ServletOutputStream outputStream = response.getOutputStream();
		this.exportRegionExcel(userList, outputStream);
		
	}
	
	public void exportRegionExcel(List<Region> regionList, ServletOutputStream outputStream) {
		try {
			//1、创建工作簿
			HSSFWorkbook workbook = new HSSFWorkbook();
			//1.1、创建合并单元格对象
			CellRangeAddress cellRangeAddress = new CellRangeAddress(0, 0, 0, 4);//起始行号，结束行号，起始列号，结束列号
			
			//1.2、头标题样式
			HSSFCellStyle style1 = createCellStyle(workbook, (short)16);
			
			//1.3、列标题样式
			HSSFCellStyle style2 = createCellStyle(workbook, (short)13);
			
			//2、创建工作表
			HSSFSheet sheet = workbook.createSheet("区域列表");
			//2.1、加载合并单元格对象
			sheet.addMergedRegion(cellRangeAddress);
			//设置默认列宽
			sheet.setDefaultColumnWidth(25);
			
			//3、创建行
			//3.1、创建头标题行；并且设置头标题
			HSSFRow row1 = sheet.createRow(0);
			HSSFCell cell1 = row1.createCell(0);
			//加载单元格样式
			cell1.setCellStyle(style1);
			cell1.setCellValue("区域列表");
			
			//3.2、创建列标题行；并且设置列标题
			HSSFRow row2 = sheet.createRow(1);
			String[] titles = {"省份","城市", "区域", "邮编"};
			for(int i = 0; i < titles.length; i++){
				HSSFCell cell2 = row2.createCell(i);
				//加载单元格样式
				cell2.setCellStyle(style2);
				cell2.setCellValue(titles[i]);
			}
			
			//4、操作单元格；将用户列表写入excel
			if(regionList != null){
				Region region = null;
				for(int j = 0; j < regionList.size(); j++){
					region = regionList.get(j);
					
					HSSFRow row = sheet.createRow(j+2);
					//设置省份
					HSSFCell cell11 = row.createCell(0);
					cell11.setCellValue(region.getProvince());
					//设置城市
					HSSFCell cell12 = row.createCell(1);
					cell12.setCellValue(region.getCity());
					//设置分区
					HSSFCell cell13 = row.createCell(2);
					cell13.setCellValue(region.getDistrict());
					
					HSSFCell cell14 = row.createCell(3);
					cell14.setCellValue(region.getPostcode());
				}
			}
			//5、输出
			workbook.write(outputStream);
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			if(outputStream != null) {
				try {
					outputStream.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
		}
	}

	/**
	 * 创建单元格样式
	 * @param workbook 工作簿
	 * @param fontSize 字体大小
	 * @return 单元格样式
	 */
	private HSSFCellStyle createCellStyle(HSSFWorkbook workbook, short fontSize) {
		HSSFCellStyle style = workbook.createCellStyle();
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中
		style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中
		//创建字体
		HSSFFont font = workbook.createFont();
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//加粗字体
		font.setFontHeightInPoints(fontSize);
		//加载字体
		style.setFont(font);
		return style;
	}
}
